package com.dhu.demo.service;

import com.dhu.demo.entity.*;
import com.dhu.demo.mapper.ScMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import org.apache.poi.ss.usermodel.CellType;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.nio.charset.Charset;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.List;

@Service
public class ScService {

    @Autowired(required = false)
    private ScMapper scMapper;

    @Autowired
    private ClassTableService classTableService;

    public List<Sc> findAll(){return scMapper.findAll();}

    //获得某学生的所有成绩
    public List<Sc> getallscore(int studentnumber,String xueqi){return scMapper.selectAllScoreByStudentnumberAndXueqi(studentnumber,xueqi);}

    public List<Sc> selectAllInfoByClassName(String classname){return scMapper.selectAllInfoByClassName(classname);}
    public List<Sc> selectAllInfoByScore(int score){return scMapper.selectAllInfoByScore(score);}

    public int deleteAllInfoByStudentnumber(int studentnumber){return scMapper.deleteAllInfoByStudentnumber(studentnumber);}
    public String deleteAllInfoByClassName(String classname){return scMapper.deleteAllInfoByClassName(classname);}
    public int insert(Sc record){
        return scMapper.insert(record);
    }
    public int deleteByPrimaryKey(ScKey record){
        return scMapper.deleteByPrimaryKey(record);
    }
    public int updateByPrimaryKey(Sc record){
        return scMapper.updateByPrimaryKey(record);
    }

    // 添加 前端上传Excel文件后端处理文件并将文件数据存入数据库的操作
    public void importFile(MultipartFile File){
        if (File == null || File.getSize() == 0){
            System.out.println("文件上传错误");
        }
        String filename = File.getOriginalFilename();
        try{
            if (filename.endsWith(".xls")){
                readXLS(File);
            }
            else if (filename.endsWith(".xlsx"))
            {
                readXLSX(File);
            }
            else if (filename.endsWith(".sql")){
                readSql(File);
            }
        }catch(IOException e){
            System.out.println("文件读取错误");
        }
    }

    public void readXLS(MultipartFile file) throws IOException{
        InputStream inputStream = file.getInputStream();
        HSSFWorkbook Workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = Workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
            HSSFRow row = sheet.getRow(rowNum);
            if (row != null){
                Sc Sc = new Sc();
                row.getCell(0).setCellType(CellType.STRING);
                row.getCell(2).setCellType(CellType.STRING);
                Sc.setStudentnumber(Integer.parseInt(row.getCell(0).getStringCellValue()));
                Sc.setClassname(row.getCell(1).getStringCellValue());
                Sc.setScore(Integer.parseInt(row.getCell(2).getStringCellValue()));
                String grade=classTableService.calculatingLevel(Sc.getClassname(),Sc.getScore());
                String xueqi="2018下";
                Sc.setGrade(grade);
                Sc.setXueqi(xueqi);
                insert(Sc);
            }
        }
    }

    public void readXLSX(MultipartFile file) throws IOException{
        InputStream inputStream = file.getInputStream();
        XSSFWorkbook Workbook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = Workbook.getSheetAt(0);
        // 第一行是表头，填写的是什么学号、课程啥的，不是具体数据，具体从第二行开始，所以少一行
        int lastRowNum = sheet.getLastRowNum();
        for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) {
            XSSFRow row = sheet.getRow(rowNum);
            if (row != null){//记得看一下row长啥样
                Sc Sc = new Sc();
                //使用了getStringCellValue()方法来获取值，POI会判断单元格的类型，如果非字符串类型就会抛出上面的异常。
                //所以先使用setCellType()方法先将该单元格的类型设置为STRING
                //然后poi会根据字符串读取它
                // 数据格式为 课程   学号    分数
                // classname  studentnumber  score
                // string   int   int
                // 0      1      2
                row.getCell(1).setCellType(CellType.STRING);
                row.getCell(2).setCellType(CellType.STRING);
                Sc.setStudentnumber(
                        Integer.parseInt(row.getCell(1).getStringCellValue()));
                Sc.setClassname(row.getCell(0).getStringCellValue());
                Sc.setScore(Integer.parseInt(row.getCell(2).getStringCellValue()));
                //由 课程 和 分数 获取当前所对应的等级
                String grade=classTableService.calculatingLevel(Sc.getClassname(),Sc.getScore());
                //等级偷个懒。。还没解决
                String xueqi="2018下";
                Sc.setGrade(grade);
                Sc.setXueqi(xueqi);
                //插入该数据
                insert(Sc);
            }
        }
    }

    public static Connection getMySqlConnection() throws Exception {
        String dbHost="localhost";
        String dbName="dbcoursedesign";
        String userName="root";
        String userPassword="123456";
        String dbPort="3306";//注意检查

        Class.forName("com.mysql.cj.jdbc.Driver");
        String url="jdbc:mysql://"+dbHost+":"+dbPort+"/"+dbName+"?useUnicode=true&characterEncoding=utf-8&port="+dbPort+"&autoReconnect=true";
        return DriverManager.getConnection(url,userName,userPassword);
    }

    public void readSql(MultipartFile file)throws IOException{


        InputStream inputStream=file.getInputStream();
        Reader readsql=new InputStreamReader(inputStream, StandardCharsets.UTF_8);

        try{
            Connection conn=getMySqlConnection();
            ScriptRunner runner=new ScriptRunner(conn);
            Resources.setCharset(StandardCharsets.UTF_8); //设置字符集
            runner.setLogWriter(null);//设置是否输出日志
            runner.runScript(readsql);
            conn.close();;
        }catch (Exception e){
            e.printStackTrace();
        }
    }



}
